Online-Academy
Look, Read, Understand, Apply

Data-Driven Decision Making

A simple example to show how Database system can help manager to make data-driven decisions. We know that their are generally four functional units in organization. Different managers work in those functional units, they have to make several decisions. Here, we will a simple example: How a Sales Manager Decides What Products to Order?

  • As a sales manager, your goal is to ensure the store always has the right products available—not too many (which increases storage cost and wastage) and not too few (which leads to stock-outs and lost sales). To make this decision, you follow a structured, data-driven process:

    • Review Past Sales Data: Manager needs sales reports from previous weeks or months to understand:

      1. Which products sell fast?
      2. Which products sell slowly?
      3. Seasonal trends (e.g., festival period, winter, summer)
      4. Weekly or weekend patterns

    • This helps manager to forecast how much stock s/he will need for the coming week.

    • Check Current Inventory Levels
    • Next, manager checks:
    • How many units you currently have in stock?
    • Items that are nearly out of stock
    • Products that are overstocked

    • This avoids unnecessary ordering and reduces excess stock.

    • Consider Supplier Factors
    • Manager also analyze:
    • Supplier lead time (how fast suppliers deliver)
    • Minimum order quantity (MOQ)
    • Available discounts or promotions
    • Reliability of suppliers
    • Products with long lead times might need to be ordered earlier.
    • Market Demand & Customer Feedback
    • Manager considers:
    • Popular customer requests
    • New trends in the market
    • Products with high profit margin
    • Items with declining demand
    • Sometimes data alone isn't enough—customer interactions help manager decide what to prioritize.
    • Financial & Storage Constraints
    • Manager checks:
    • Budget for purchase this week
    • Storage space availability
    • Product shelf-life (especially perishables)
    • Products that expire quickly must be ordered carefully.
    • Set Reorder Point (ROP)
    • For each product, manager defines:
    • Reorder Point = Average daily usage * Lead time + Safety stock
    • If current stock falls below this point, manager order immediately.

    To answer above questions, manager needs data, process those data, get information. To process data and get information properly data has to be stored properly. Database management system helps to store data correctly.

    • What Database System Will Help?
    • A Relational Database Management System (RDBMS) combined with Inventory Management Software is ideal.
    • These systems help manager store and analyze:
    • Sales data (sales table)
    • Inventory data (stock table)
    • Supplier information (supplier table)
    • Order history (purchase order table)
    • Customer feedback (customer table)

    In Short:

    To decide what products to order, a sales manager analyzes past sales data, current stock levels, demand trends, customer preferences, and supplier conditions. A Relational Database System (RDBMS) along with inventory management software helps store sales, stock, and supplier data and provides quick reports. Advanced DSS or ERP systems further help in forecasting and decision-making.

    1. Decision Rules
    2. Manager decides ordering based on:
    3. If current stock < reorder level -> ORDER
    4. If product sold heavily last week -> PRIORITY ORDER
    5. If stock is high & sales low -> DO NOT ORDER

      6. Data about product, inventory, and weekly_sales will be of great help for manager to get answer for his/her questions.

      First create a database named: DDD in mysql using following command in WorkBench. You can create database without work bench also.

    6. create database DDD;
      7. Make this database active by running following command:
    7. use DDD;
    8. Then create following tables using following create table statements. Run following create table statements in work bench with your DDD database active.

      9. -- PRODUCT TABLE
CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    category VARCHAR(30),
    reorder_level INT
);

-- INVENTORY TABLE
CREATE TABLE inventory (
    product_id INT,
    current_stock INT,
    PRIMARY KEY (product_id),
    FOREIGN KEY (product_id) REFERENCES product(product_id)
);

-- WEEKLY SALES TABLE
CREATE TABLE weekly_sales (
    product_id INT,
    last_week_sales INT,
    PRIMARY KEY (product_id),
    FOREIGN KEY (product_id) REFERENCES product(product_id)
);

      Lets suppose manager has following data inserted to the three tables.

      -- Insert products
INSERT INTO product (product_id, product_name, category, reorder_level) VALUES
(1, 'Milk', 'Dairy', 20),
(2, 'Bread', 'Bakery', 15),
(3, 'Rice', 'Grocery', 30),
(4, 'Sugar', 'Grocery', 25),
(5, 'Eggs', 'Dairy', 50);

-- Insert inventory data
INSERT INTO inventory (product_id, current_stock) VALUES
(1, 10),
(2, 18),
(3, 35),
(4, 10),
(5, 70);

-- Insert sales data
INSERT INTO weekly_sales (product_id, last_week_sales) VALUES
(1, 25),
(2, 12),
(3, 20),
(4, 30),
(5, 40);
      How to know what are the products needed to be ordered? Manager has to fire following query to SELECT Products That Need to Be Ordered 
      SELECT 
    p.product_id,
    p.product_name,
    i.current_stock,
    p.reorder_level
FROM product p
JOIN inventory i ON p.product_id = i.product_id
WHERE i.current_stock < p.reorder_level;
      Following select statement will SELECT Products With High Sales But Low Stock 
          SELECT 
    p.product_name,
    i.current_stock,
    p.reorder_level,
    s.last_week_sales
    FROM product p
    JOIN inventory i ON p.product_id = i.product_id
    JOIN weekly_sales s ON p.product_id = s.product_id
    WHERE i.current_stock < p.reorder_level
    AND s.last_week_sales > 20;
      SELECT Products With Enough Stock (No Order Needed) 
          SELECT 
    p.product_name,
    i.current_stock,
    p.reorder_level
    FROM product p
    JOIN inventory i ON p.product_id = i.product_id
    WHERE i.current_stock >= p.reorder_level;

      SELECT Slow-Moving Products

          SELECT 
    p.product_name,
    s.last_week_sales
    FROM product p
    JOIN weekly_sales s ON p.product_id = s.product_id
    WHERE s.last_week_sales < 15;

      
SELECT Fast-Moving Products

      SELECT 
    p.product_name,
    s.last_week_sales
    FROM product p
    JOIN weekly_sales s ON p.product_id = s.product_id
    WHERE s.last_week_sales >= 20;